package demo;

import javax.servlet.*;
import javax.servlet.http.*;

import com.mysql.jdbc.*;

import java.io.*;


public class FoodSelection extends HttpServlet {

	/**
	 * Process the HTTP Post request
	 * 
	 * @param request
	 *            an object holding the information sent by the client
	 * @param response
	 *            an object holding the information sent to the client
	 * @throws ServletException
	 *             , IOException
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		DBConnector db = DBConnector.getDBConnector();
		String resultURL = "<script type =\"text/javascript\"> window.location.href = '../search_result.jsp'</script>";
		PrintWriter out = response.getWriter();		
		
		//Get the user input information about the food selected.
		String FoodSelected = (String)request.getParameter("food_selected");
		String FoodQuantity = (String)request.getParameter("food_quantity");
		
		//Message the user that the food has been selected, return back to the index page	
		if (FoodSelected == null)
		{
			out.write(resultURL);
			return;
		}
			
		out.println("<script>alert(unescape('you have selected " + FoodQuantity + " " + FoodSelected + "'))</script>");		
				
		//check whether the current user has already started an order, 
		//whether an order form is of status not cooked yet
		String UserID = "1";
		String GetFormID = "select form_ID from order_form where cus_ID = " + UserID + " and form_state = 'not cooked yet'";
				
		String InsertOrderFormItem = "insert into order_form values(null, 1, now(),'not cooked yet', null, null, null); ";

		String NewOrderFormID = "select form_ID from order_form where form_time = (select max(form_time) from order_form where cus_ID = " + UserID + ")";
		
		String GetFoodID = "select food_ID from food where food_name = '" + FoodSelected + "'";
	
		try {
			//get the selected food ID
			db.prepareStatement(GetFoodID);			
			ResultSet FoodID = db.queryPstmt();
			
			//the food_ID string for the currently selected food
			FoodID.next();
			String Food_ID = FoodID.getString(1); 
			
			db.prepareStatement(GetFormID);
			ResultSet FormID = db.queryPstmt();		
			
			//if the form is not existing,  then create a new form for the current user
			//also create new contain items according to the food selected and the new order form ID
			if (!FormID.next())
			{
				//add a new order form
				db.prepareStatement(InsertOrderFormItem);
				db.updatePstmt();//pstmt2.executeUpdate();
			
				//get the new order form ID
				db.prepareStatement(NewOrderFormID);
				ResultSet newFormID = db.queryPstmt();
				
				newFormID.next();
				String newForm_ID = newFormID.getString(1);
				
				//create a new item for contain				
				String InsertContainItem = "insert into contain values(null, " + newForm_ID + ", " + Food_ID + ", " +  FoodQuantity + ")";
				db.prepareStatement(InsertContainItem);
				db.updatePstmt();
			}
			//if the form is existing, then continue to add the selected food to the same order form
			//in contain, check whether there are existing items that corresponding to the currently selected food
			else
			{
				//if the current food exist, do not add new item, instead, update the quantity attribute
				String oldForm_ID = FormID.getString(1);
				String CheckFoodItemExist = "select food_ID from contain where form_ID = " + 
											oldForm_ID + " and food_ID = " + Food_ID;		
				
				db.prepareStatement(CheckFoodItemExist);
				ResultSet foodIDFound = db.queryPstmt();				
				
				//no existing food items in the contain, insert a new item into contain
				if (!foodIDFound.next())
				{
					String InsertOldContainItem = "insert into contain values(null, " + oldForm_ID + ", " + Food_ID + ", " +  FoodQuantity + ")";
					db.prepareStatement(InsertOldContainItem);
					db.updatePstmt();					
				}
				//exists food items in contain already, update the quantity
				else
				{
					String UpdateContain = "update contain set order_quantity = order_quantity + " + FoodQuantity + " where form_ID = " + oldForm_ID + " and food_ID = " + Food_ID;
					
					db.prepareStatement(UpdateContain);
					db.updatePstmt();								
				}
			}
			
			out.write(resultURL);
		} catch (Exception ex) {
			out.println("Unfortunately, there are errors : " + ex.getMessage());
		} finally {
			out.close(); // Close stream
		}
	}
}
